'Reference Oracle.DataAccess found in C:\app\LCW\product\11.1.0\client_1\ODP.NET\bin\2.x
'Note: like Paradox and Access, user must do separate installation of Oracle libraries (found at http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft1110710beta.html)
Imports Oracle.DataAccess.Client

'if reference System.Data.OracleClient
'Imports System.Data.OracleClient

''' <summary>
''' Oracle (note: added several overrides so doesn't have to rely on slow GetSchema calls)
''' </summary>
''' <remarks>The default administrative login is system/(no default pswd)</remarks>
Public Class clsDBOracle
    Inherits clsDB

    ''' <summary>
    ''' Return a copy of the current generic connection
    ''' </summary>
    ''' <returns>Clone of current instance</returns>
    Public Overrides Function Clone() As clsDB
        Dim db As clsDB = New clsDBOracle
        MyBase.CloneSelf(db)
        Return db
    End Function


    ''' <summary>
    ''' Provider-independent access to CommandBuilder object
    ''' </summary>
    ''' <param name="da">Data adapter</param>
    ''' <returns>CommandBuilder object specific to provider type</returns>
    Public Overrides Function CommandBuilder(ByVal da As DbDataAdapter) As DbCommandBuilder
        Return New OracleCommandBuilder(da)
    End Function

    ''' <summary>
    ''' Return a data adapter for the current connection containing data from sql string
    ''' </summary>
    ''' <param name="SqlString">standard sql string</param>
    ''' <returns>Data Adapter</returns>
    Public Overrides Function DataAdapter(ByVal SqlString As String) As DbDataAdapter
        If Connection Is Nothing Then Return Nothing
        If OpenConnection() Then Return New OracleDataAdapter(SqlString, CType(Connection, OracleConnection)) Else Return Nothing
    End Function

    ''' <summary>
    ''' Create a parameter (used to speed up queries and updates); value is initialized to dbnull
    ''' </summary>
    ''' <param name="ParameterName">name of parameter (usually field name)</param>
    ''' <param name="DataType">need to work on this to standarize</param>
    ''' <param name="Size">size if appropriate (strings)</param>
    ''' <returns>Data Parameter</returns>
    Public Overrides Function Parameter(ByRef Command As DbCommand, ByVal ParameterName As String, ByVal DataType As System.Type, Optional ByVal Size As Int16 = 0) As DbParameter
        If Connection Is Nothing Then Return Nothing
        Dim param As OracleParameter

        'following for Oracle provider

        Dim SqlType As OracleDbType
        If DataType Is GetType(String) Then
            SqlType = OracleDbType.Varchar2
        ElseIf DataType Is GetType(Date) Then
            SqlType = OracleDbType.Date
        ElseIf DataType Is GetType(Double) Then
            SqlType = OracleDbType.Double
        ElseIf DataType Is GetType(Integer) Then
            SqlType = OracleDbType.Int32
        ElseIf DataType Is GetType(Boolean) Then
            SqlType = OracleDbType.Byte
        ElseIf DataType Is GetType(Short) Then
            SqlType = OracleDbType.Int16
        Else
            Throw New ApplicationException("Invalid type passed to Parameter")
            Return Nothing
        End If

        'following for MS Oracle provider

        'Dim SqlType As OracleType
        'If DataType Is GetType(String) Then
        '    SqlType = OracleType.VarChar
        'ElseIf DataType Is GetType(Date) Then
        '    SqlType = OracleType.DateTime
        'ElseIf DataType Is GetType(Double) Then
        '    SqlType = OracleType.Double
        'ElseIf DataType Is GetType(Integer) Then
        '    SqlType = OracleType.Int32
        'ElseIf DataType Is GetType(Boolean) Then
        '    SqlType = OracleType.Byte
        'ElseIf DataType Is GetType(Short) Then
        '    SqlType = OracleType.Int16
        'Else
        '    Throw new applicationexception("Invalid type passed to Parameter")
        '    Return Nothing
        'End If
        'If Size > 0 Then
        '    param = New OracleParameter(ParameterName, SqlType, Size)
        'Else
        '    param = New OracleParameter(ParameterName, SqlType)
        'End If
        'param.Value = DBNull.Value
        param = Command.CreateParameter
        With param
            .ParameterName = ParameterName
            .OracleDbType = SqlType
            If Size > 0 Then .Size = Size
            .Value = DBNull.Value
        End With
        Return param
    End Function

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        Try
            Dim csb As New OracleConnectionStringBuilder
            With csb
                .DataSource = DataSource
                .UserID = UserName
                .Password = Password
                Connection = New OracleConnection(.ConnectionString)
            End With
        Catch ex As System.TypeInitializationException
            Throw New ApplicationException("Unable to connect to Oracle because you do not have the necessary Oracle client libaries installed (these are not automatically installed by WRDB). Please download and install these client libraries before trying to access an Oracle database (or use a different database type). Note that you must also install Oracle Database Access Components (ODAC) libraries available from www.Oracle.com (search for 'download ODAC').", ex)
            Connection = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Return a list of columns for this table in the original order (all fields are converted to UPPERCASE)
    ''' </summary>
    ''' <param name="TableName">Name of table</param> 
    ''' <returns></returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Overrides Function GetColumnList(ByVal TableName As String) As Generic.List(Of String)
        Try
            If Not OpenConnection() Then Return Nothing

            Dim col As New Generic.List(Of String)

            'Dim dr As DbDataReader = ExecuteReader(String.Format("SELECT column_name FROM user_tab_cols WHERE table_name='{0}'", TableName.ToUpper))
            Dim dr As DbDataReader = ExecuteReader(String.Format("SELECT column_name FROM all_tab_cols WHERE table_name='{0}' AND owner='WRDB' ORDER BY column_id", TableName.ToUpper))
            While dr IsNot Nothing AndAlso dr.Read
                col.Add(dr.GetString(0).ToUpper)
            End While
            dr.Close()
            Return col
        Catch ex As Exception
            Return New Generic.List(Of String) 'return empty list to avoid later errors
        Finally

            'DbConnection.Close()
        End Try
    End Function

    ''' <summary>
    ''' Return a list of tables or views for this project
    ''' </summary>
    ''' <returns>List of table names</returns>
    Private Function GetTable_ViewList(ByVal IsTable As Boolean) As Generic.List(Of String)
        Try
            If Not OpenConnection() Then Return Nothing

            Dim col As New Generic.List(Of String)
            'Dim dr As DbDataReader = ExecuteReader("SELECT {0}_name FROM user_{0}s", IIf(IsTable, "table", "view"))
            'following change requires that table be placed in tablespace called WRDB; allows multiple users into same database (e.g., full/partial privelege)
            Dim dr As DbDataReader = ExecuteReader("SELECT {0}_name FROM all_{0}s WHERE owner='WRDB'", IIf(IsTable, "table", "view"))
            While dr IsNot Nothing AndAlso dr.Read
                col.Add(Proper(dr.GetString(0)))
            End While
            dr.Close()
            Return col
        Catch ex As Exception
            Throw
            Return Nothing
        Finally
            'If DbConnection IsNot Nothing Then DbConnection.Close()
        End Try
    End Function

    ''' <summary>
    ''' Return a list of tables for this project
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Overrides Function GetTableList() As Generic.List(Of String)
        Return GetTable_ViewList(True)
    End Function

    ''' <summary>
    ''' Return a list of views for this project
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Overrides Function GetViewList() As Generic.List(Of String)
        Return GetTable_ViewList(False)
    End Function

    ''' <summary>
    ''' Return list of indexed column for this table (all fields are converted to UPPERCASE)
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>List of indexed fields (not including RecID) or Nothing if error occurred</returns>
    Public Overrides Function GetIndexList(ByVal TableName As String) As Generic.List(Of String)
        Try
            If Not OpenConnection() Then Return Nothing

            Dim lstIndex As New Generic.List(Of String)
            'Dim dr As DbDataReader = ExecuteReader(String.Format("SELECT index_name FROM user_indexes WHERE table_name='{0}'", TableName.ToUpper))
            Dim dr As DbDataReader = ExecuteReader(String.Format("SELECT index_name FROM all_indexes WHERE table_name='{0}' AND owner='WRDB'", TableName.ToUpper))
            While dr IsNot Nothing AndAlso dr.Read
                Dim idx As String = dr.GetString(0)
                If idx.ToUpper <> "RECID" Then lstIndex.Add(idx.ToUpper.Replace(TableName.ToUpper & "_", ""))
            End While
            dr.Close()
            Return lstIndex
        Catch ex As Exception
            Throw
            Return Nothing
        Finally
            'DbConnection.Close()
        End Try
    End Function

    ''' <summary>
    ''' Return a list of reserved words for this provider
    ''' </summary>
    Public Overrides Function GetReservedWords() As Generic.List(Of String)
        Try
            Dim col As New Generic.List(Of String)
            col.AddRange(New String() {"ACCESS", "ADD", "ALL", "ALTER", "AND", "ANY", "ARRAYLEN", "AS", "ASC", "AUDIT", "BETWEEN", "BY", "CHAR", "CHECK", "CLUSTER", "COLUMN", "COMMENT", "COMPRESS", "CONNECT", "CREATE", "CURRENT", "DATE", "DECIMAL", "DEFAULT", "DELETE", "DESC", "DISTINCT", "DROP", "ELSE", "EXCLUSIVE", "EXISTS", "FILE", "FLOAT", "FOR", "FROM", "GRANT", "GROUP", "HAVING", "IDENTIFIED", "IMMEDIATE", "IN", "INCREMENT", "INDEX", "INITIAL", "INSERT", "INTEGER", "INTERSECT", "INTO", "IS", "LEVEL", "LIKE", "LOCK", "LONG", "MAXEXTENTS", "MINUS", "MODE", "MODIFY", "NOAUDIT", "NOCOMPRESS", "NOT", "NOTFOUND", "NOWAIT", "NULL", "NUMBER", "OF", "OFFLINE", "ON", "ONLINE", "OPTION", "OR", "ORDER", "PCTFREE", "PRIOR", "PRIVILEGES", "PUBLIC", "RAW", "RENAME", "RESOURCE", "REVOKE", "ROW", "ROWID", "ROWLABEL", "ROWNUM", "ROWS", "START", "SELECT", "SESSION", "SET", "SHARE", "SIZE", "SMALLINT", "SQLBUF", "SUCCESSFUL", "SYNONYM", "SYSDATE", "TABLE", "THEN", "TO", "TRIGGER", "UID", "UNION", "UNIQUE", "UPDATE", "USER", "VALIDATE", "VALUES", "VARCHAR", "VARCHAR2", "VIEW", "WHENEVER", "WHERE", "WITH"})
            Return col
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

End Class
